package com.eascs.banknet.boc.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.banknet.common.dao.BaseO2oDao;
import com.eascs.banknet.common.entity.LoanUserEntity;
import com.eascs.banknet.common.entity.LoanapplyEntity;
import com.eascs.banknet.common.entity.O2oPaymentsPlan;

@Repository
public class LoanapplyDao extends BaseO2oDao {
	public LoanUserEntity getLoanUserByAppno(String appno){
		String sql = "select * from o2o_loan_user where bank_applyno = ?";
		return super.findEntity(sql, LoanUserEntity.class, appno);
	}
	
	public String getLoanapplyNoByAppno(String appno){
		String sql = "select loanapply_no from o2o_loan_user where bank_applyno = ?";
		return super.findString(sql, appno);
	}
	
	public LoanapplyEntity getLoanEntityNoByAppno(String appno){
		StringBuffer sql = new StringBuffer();
		sql.append("select lpy.* from o2o_loanapply lpy where lpy.loanapply_no in ")
			.append("(select user.loanapply_no from o2o_loan_user user where user.bank_applyno = ?)");
		return super.findEntity(sql.toString(), LoanapplyEntity.class, appno);
	}
	
	public String getBillCodeByAppno(String loanapplyNo){
		String sql = "select bill_code from o2o_loanapply where loanapply_no = ?";
		return super.findString(sql, loanapplyNo);
	}
	
	public LoanapplyEntity getLoanEntityByActno(String bankAccount){
		String sql = "select * from o2o_loanapply where bank_actno = ? and bank_type='BC' and loan_flg = 'Y' ";
		return super.findEntity(sql, LoanapplyEntity.class, bankAccount);
	}
	
	public String getMainAppno(String loanapplyNo){
		String sql = "select bank_applyno from o2o_loan_user where loanapply_no = ? and spouse_id is null";
		return super.findString(sql, loanapplyNo);
	}
	
	/**
	 * 查询主贷款人
	 * @param loanapplyNo
	 * @return
	 */
	public LoanUserEntity getMainLoanUser(String loanapplyNo){
		String sql = "select * from o2o_loan_user where loanapply_no = ? and spouse_id is null";
		return super.findEntity(sql,LoanUserEntity.class, loanapplyNo);
	}
	/**
	 * 获取款计划
	 * @param billCode
	 * @param terms
	 * @return
	 */
	public O2oPaymentsPlan getO2OPaymentsPlanByBillCodeAndTerms(String billCode,int terms){
		String sql = "SELECT * from o2o_payments_plan t where t.bill_code= ? and t.periods = ? and is_del = 'N'";
		return super.findEntity(sql,O2oPaymentsPlan.class,billCode,terms);
	}
	
	/**
	 * 查询
	 * @return
	 */
	public List<LoanUserEntity> getLoanList(){
		StringBuffer sql = new StringBuffer();
		sql.append("select user.* from o2o_loanapply lpy, o2o_loan_user user")
		.append("	where lpy.loanapply_no = user.loanapply_no and (lpy.data_source is null or lpy.data_source!='2') ")
		.append(" and user.bank_applyno is not null and islock = 'N'")
		.append(" and lpy.wf_type != 'O' and lpy.bank_type='BC' ")
		.append(" and spouse_id is null order by user.bank_applyno");
		return super.findEntitys(sql.toString(), LoanUserEntity.class);
	}
	
	/**
	 * 拉取征信报告数据
	 * @return
	 */
	public List<LoanUserEntity> getPullZXBGDataList(){
		StringBuffer sql = new StringBuffer();
		sql.append("select lu.* from o2o_loanapply lpy, o2o_loan_user lu")
		.append("	where lpy.loanapply_no = lu.loanapply_no and (lpy.data_source is null or lpy.data_source!='2') ")
		.append(" and lu.bank_applyno is not null and islock = 'N' AND lpy.wf_type !='T' and lu.bank_applyno is not null ")
		.append(" and lpy.wf_type != 'O' and lpy.bank_type='BC' ")
		.append(" and NOT EXISTS (SELECT 1 FROM o2o_datalist t3 ")
		.append(" WHERE t3.loanapply_no = lpy.loanapply_no and t3.is_delete = 'N' ")
		.append(" AND ((lu.sponsor_type is null and lu.`spouse_id` IS NULL AND t3.datalist_code ='ZXBGDATA') ")
		.append(" OR (lu.sponsor_type is null and lu.`spouse_id` IS NOT NULL AND t3.datalist_code ='POZXBGDATA') ")
		.append(" OR (lu.sponsor_type = 'D' and t3.datalist_code = 'SPONSOR_ZXBG' and t3.sponsor_id is not null and lu.id = t3.sponsor_id)))")
		//.append(" OR (lu.sponsor_type = 'D' and t3.datalist_code = 'SPONSOR_ZXBG')))")
		.append(" order by lu.bank_applyno");
		return super.findEntitys(sql.toString(), LoanUserEntity.class);
	}
	
	/**
	 * 拉取贷款合同的数据
	 * @return
	 */
	public List<LoanUserEntity> getPullDKHTDataList(){
		StringBuffer sql = new StringBuffer();
		sql.append("select lu.* from o2o_loanapply lpy, o2o_loan_user lu")
		.append("	where lpy.loanapply_no = lu.loanapply_no and (lpy.data_source is null or lpy.data_source!='2') ")
		.append(" and lu.bank_applyno is not null and islock = 'N' AND lpy.wf_type !='T' and lu.bank_applyno is not null")
		.append(" and lpy.wf_type != 'O' and lpy.bank_type='BC' ")
		.append(" and spouse_id is null and lpy.bank_osn is not null ")
		.append(" and NOT EXISTS (SELECT 1 FROM ")
		.append(" o2o_datalist t3 WHERE t3.loanapply_no = lpy.loanapply_no and t3.is_delete = 'N' ")
		.append(" AND t3.datalist_code = 'REFUNDCONTRACTBAK') ")
		.append(" order by lu.bank_applyno limit 5");
		return super.findEntitys(sql.toString(), LoanUserEntity.class);
	}
	
	/**
	 * 获取需要上传申报资料的贷款单
	 * @return
	 */
	public List<LoanUserEntity> getSendBankFtpApplyList(){
		StringBuffer sql = new StringBuffer();
		sql.append("select user.* from o2o_loanapply lpy, o2o_loan_user user")
		.append("	where lpy.loanapply_no = user.loanapply_no and (lpy.data_source is null or lpy.data_source!='2') ")
		.append(" and user.bank_applyno is not null and islock = 'N'")
		.append(" and lpy.wf_type = 'S' and lpy.bc_flag = 'Y' ")
		.append(" and lpy.bank_type='BC' and user.spouse_id is null ")
		.append(" and EXISTS(")
		.append(" 	select 1 from o2o_datalist dt where dt.loanapply_no=lpy.loanapply_no")
		.append(" 	and is_ftp='N' and datalist_code='BANKAPPLYDATA' and is_delete = 'N' )")
		.append(" order by user.bank_applyno");
		return super.findEntitys(sql.toString(), LoanUserEntity.class);
	}
	
	public String isBillCodeExist(String  billCode){
		String key_code = super.findString("select t.bank_loan_id from o2o_bank_loan_data t where t.is_del ='N' and t.bill_code = ?",billCode);
		return key_code==null?"":key_code;
	}
}
